package dao.mysql;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import dao.CompensationDAO;
import dao.ConnPool;
import dao.DAOException;
import dto.CompensationDTO;

public class CompensationDAOImpl implements CompensationDAO
{
	Statement stmt = null;
	ResultSet result = null;

	public ArrayList<CompensationDTO> loadCompensationDTO(ResultSet result)
	{
		ArrayList<CompensationDTO> compList = new ArrayList<CompensationDTO>();

		try
		{
			while (result.next())
			{
				CompensationDTO chosenComp = new CompensationDTO();
				chosenComp.set_approvingManager(result
						.getInt("ApprovingManager"));
				chosenComp.set_userID(result.getInt("UserID"));
				chosenComp.set_compID(result.getInt("CompID"));
				chosenComp.set_numberOfDays(result.getInt("NumberOfDays"));
				chosenComp.set_userName(result.getString("UserName"));
				chosenComp.set_status(result.getString("Status"));

				compList.add(chosenComp);
			}
		}
		catch (Exception e)
		{
			System.out.println("Error Occurred at load data");
			System.out.println(e.toString());
		}

		return compList;
	}

	public ArrayList<CompensationDTO> getAllCompApplication(int appmanID)
			throws DAOException
	{
		ArrayList<CompensationDTO> compensationList = new ArrayList<CompensationDTO>();

		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String
					.format("Select * from ca_schemata.Compensation WHERE ApprovingManager = %d AND Status = 'Pending'",
							appmanID);

			result = stmt.executeQuery(sql);

			compensationList = loadCompensationDTO(result);
		}
		catch (Exception e)
		{
			System.out.println("find all compensation error");
			System.out.println(e.toString());
			compensationList = null;
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}

		return compensationList;
	}

	@Override
	public void insertCompApplication(CompensationDTO comp)
			throws DAOException
	{
		// TODO Auto-generated method stub

		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String
					.format("INSERT INTO ca_schemata.Compensation (UserID, UserName, ApprovingManager, NumberOfDays, Status) values ( %d, '%s', %d, %d, '%s')",
							comp.get_userID(), comp.get_userName(),
							comp.get_approvingManager(),
							comp.get_numberOfDays(), comp.get_status());

			stmt.execute(sql);
		}
		catch (Exception e)
		{
			System.out.println("insert compensation error");
			System.out.println(e.toString());
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}
	}

	@Override
	public void updateCompApplicationStatus(CompensationDTO comp) throws DAOException
	{
		// TODO Auto-generated method stub
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String
					.format("UPDATE ca_schemata.Compensation SET Status ='%s' WHERE CompID = %d", 
							comp.get_status(), comp.get_compID());

			stmt.execute(sql);
		}
		catch (Exception e)
		{
			System.out.println("update compensation error");
			System.out.println(e.toString());
		}
	}
}
